The problem of contention among database transactions is known in the art. Contention may be thought of as the competition among various database processes that access a data structure.
Contention for access to a data table occurs, for example, if one process of a computer application program requires that one or more new rows of data be inserted into a data table while another process requires that another row of data be modified. Typically, the processes are performed in the order the processing requests are received by the database management system. If the insert process was first received, therefore, the data table may be “locked” by the database management system for a period of time during which the new rows are being inserted into the data table. While the data table is locked for the insert process, no other database process can access the data table, which slows processing time and is, therefore, undesirable. Locking also increases the overhead (e.g., computer memory and processing capacity) with processing database transactions.
As can be appreciated, the issue of contention becomes more acute as the number of database processes that need simultaneous access to a transactional database at a given time increases. A transactional database, which typically uses one or more staging tables, contains dynamic data, which means the database receives a large number of simultaneous requests to insert new data, or modify or delete existing data, in the database. Thus, a transactional database is inherently contentious. A transactional database can be distinguished from a data warehouse, which stores a large volume of generally static data of a historical nature on a permanent basis. Once data is stored in a data warehouse, it is not typically modified or deleted.
Prior art methods of improving the performance of, and minimizing contention among database processes that need access to large tables used in a data warehouse, rely on partitioning a data table. Partitioning of a database can be thought of as dividing a logical database, or its constituting elements, into distinct independent parts. A database can be partitioned either by building separate smaller databases (each with its own tables, indices, and transaction logs), or by splitting selected elements, for example just one table. “Horizontal partitioning” involves putting different rows into different tables. Horizontal partitions are created by defining a partition key, i.e., selecting a data element or a column of the table, defining one or more partitions, and then defining a range of values found in the selected data element for each partition. For example, customers with ZIP codes less than 50000 are stored in a first partition, e.g., CustomersEast, while customers with ZIP codes greater than or equal to 50000 are stored in a second partition, e.g., CustomersWest.
Compared to a data warehouse, transactional databases include relatively dynamic data and do not typically include large volumes of data in a single table. Thus, known partitioning methods are not indicated for reducing contention and improving throughput in transactional databases. Furthermore, because of the dynamic nature of the data in a transactional database, prior art methods of partitioning are not suitable due to the inherent difficulties associated with selection of a proper partition key and properly defining the range of values in each partition.
Other prior art approaches to minimizing contention in a high volume transactional database include queuing and process scheduling. The disadvantage of both of these approaches, however, is that the amount of time to process the requested transactions increases linearly, if not exponentially, as the number of queued or scheduled requests increases. Thus, if immediate processing of a high volume of database transactions is required, such approaches do not yield acceptable results.
Thus, what is needed is a computer implemented system and method, and a computer program product, for immediately processing a high volume of contentious database transactions without the disadvantages associated with the prior art methods.